﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Web;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Xml;
using System.IO;


namespace WcfService2   
{
    // NOTE: You can use the "Rename" command on the "Refactor" menu to change the class name "Service1" in code, svc and config file together.
    public class Service1 : IService1
    {
        
        string db_values;
        int[] db_rollnum;

        
        public string GetData(int value)
        {

            var dbNorthwind = new Sample_AppDataContext();            
            var query = from c in dbNorthwind.Sample_Apps
                        where c.Roll_Number==value
                        select c;

            foreach (var c in query)
            {
                db_values =  c.Age.Trim() + "," + c.First_Name + "," + c.Last_Name + "," + c.Marks_Obtained.Trim() + "," + c.Roll_Number ;
            }

            return db_values;
        }

        public int[] GetRollNumbers()
        {

            var dbNorthwind = new Sample_AppDataContext();            
            var query = from c in dbNorthwind.Sample_Apps
                        select c;
            //query.Count();
            db_rollnum=new int[query.Count()];
            int count=0;
            foreach (var c in query)
            {
                db_rollnum[count] = Convert.ToInt16( c.Roll_Number);
                count = count + 1;
            }

            return db_rollnum;
        }

        public void Enroll_Student(string f_name,string l_name,int age,int mo)
        {
            var dbNorthwind = new Sample_AppDataContext();
            Sample_App tb_sample_app = new Sample_App();
            tb_sample_app.Age=age.ToString();
            tb_sample_app.First_Name=f_name;
            tb_sample_app.Last_Name=l_name;
            tb_sample_app.Marks_Obtained=mo.ToString();
            dbNorthwind.Sample_Apps.InsertOnSubmit(tb_sample_app);
            dbNorthwind.SubmitChanges();            
            

        }

        public DataSet Display()
        {
            var dbNorthwind = new Sample_AppDataContext();
            var query = from c in dbNorthwind.Sample_Apps
                        select c;
            
            SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["Sample_AppConnectionString"].ConnectionString);
            string sql = @"select * from Sample_App";
            conn.Open();
            SqlDataAdapter da = new SqlDataAdapter(sql, conn);
            DataSet ds = new DataSet();
            da.Fill(ds);
            return ds;
            
          
        }

        public void Update_Student(string f_name, string l_name, int age, int mo,int roll)
        {

            var dbNorthwind = new Sample_AppDataContext();
            Sample_App tb_sample_app = new Sample_App();
            var query = from c in dbNorthwind.Sample_Apps
                        where c.Roll_Number==roll
                        select c;
            foreach (var c in query)
            {
                c.First_Name = f_name;
                c.Last_Name=l_name;
                c.Age = age.ToString();
                c.Marks_Obtained = mo.ToString();
                
           }
            
            dbNorthwind.SubmitChanges( );

        }

        public void Delete_Student(int roll)
        {

            var dbNorthwind = new Sample_AppDataContext();
            Sample_App tb_sample_app = new Sample_App();
            var query = from c in dbNorthwind.Sample_Apps
                        where c.Roll_Number == roll
                        select c;
            foreach (var c in query)
            {

                dbNorthwind.Sample_Apps.DeleteOnSubmit(c);
            }
            
            dbNorthwind.SubmitChanges();

        }

        public Emp Complex()
        {
            Emp emp = new Emp(1, "David", "Smith", 10000);
            return emp;
                      

        }
        public void Create_File()
        {
            Employee[] employees = new Employee[4];
                employees[0] = new Employee(1, "David", "Smith", 10000);
                employees[1] = new Employee(3, "Mark", "Drinkwater", 30000);
                employees[2] = new Employee(4, "Norah", "Miller", 20000);
                employees[3] = new Employee(12, "Cecil", "Walker", 120000);

                using (XmlWriter writer = XmlWriter.Create("D:\\employees.xml"))
                {
                    writer.WriteStartDocument();
                    writer.WriteStartElement("Employees");

                    foreach (Employee employee in employees)
                    {
                        writer.WriteStartElement("Employee");

                        writer.WriteElementString("ID", employee.Id.ToString());
                        writer.WriteElementString("FirstName", employee.FirstName);
                        writer.WriteElementString("LastName", employee.LastName);
                        writer.WriteElementString("Salary", employee.Salary.ToString());

                        writer.WriteEndElement();
                    }

                    writer.WriteEndElement();
                    writer.WriteEndDocument();
                }

            }

        public Dictionary<int, string> GetRollNumbers1()
        {

            var dbNorthwind = new Sample_AppDataContext();
            var query = from c in dbNorthwind.Sample_Apps
                        select c;
            query.Count();
            db_rollnum = new int[query.Count()];
            Dictionary<int, string> coln_new = new Dictionary<int, string>();
            int count = 0;
            foreach (var c in query)
            {
                coln_new.Add(System.Convert.ToInt32(c.Roll_Number), c.First_Name);
                count = count + 1;
            }

            return coln_new;
        }


        public List<string> GetFirstNameList()
        {

            var dbNorthwind = new Sample_AppDataContext();
            var query = from c in dbNorthwind.Sample_Apps
                        select c;
            query.Count();
            db_rollnum = new int[query.Count()];
            List<string> coln_new = new List<string>();
            int count = 0;
            foreach (var c in query)
            {
                coln_new.Add(c.First_Name);
                count = count + 1;
            }

            return coln_new;
        }

        public int mergeExample()
        {
            return 5;
        }

        public string mergeExample1()
        {
            return "Test";
        }


        public Student StudentDetails()
        {
            Student myObj=new Student();
            myObj.First_Name1="vipul";
            myObj.Last_Name1="mehta";
            myObj.Age1=23;
            myObj.Roll_Number1=19;
            myObj.Marks_Obtained1=23;
            myObj.AdultOrNot1=null;
            return myObj;
        }
        }
     
  }


